import pandas as pd
from ListUtils import anti_join
from DatetimeUtils import choose_day
import time


"""
股票质押统计数据
"""



def deal_append(ts_code, end_date, engine, pro):
    """
    通过anti_join函数处理增量数据
    """
    source_list_ = pro.pledge_stat(ts_code=ts_code)
    # 最好是单元数组，不然merge之后会有问题。
    sql_ = 'select s.ts_code, s.end_date from pledge_stat s'
    if ts_code and end_date:
        sql_ = sql_ + ' WHERE s.ts_code = "' + ts_code + '" and s.end_date = ' + end_date
        source_list_ = pro.pledge_stat(end_date=end_date, ts_code=ts_code)
    elif ts_code :
        sql_ = sql_ + ' WHERE s.ts_code = "' + ts_code + '"'
    else:
        sql_ = sql_ + ' WHERE s.end_date = ' + end_date
        source_list_ = pro.pledge_stat(end_date=end_date)
    target_list_ = pd.read_sql(sql_, engine)
    if len(target_list_) == 0:
        # 数据库内未找到数据则全部添加
        source_list_.to_sql('pledge_stat', con=engine, index=False, index_label=['ts_code', 'end_date'], if_exists='append')
        print('数据库内未找到数据则全部添加：(%s)' % source_list_)
        time.sleep(2)

    else:
        # 数据库内有历史数据则增量添加
        source_list_ = anti_join(source_list_, target_list_, ['ts_code', 'end_date'])
        if len(source_list_) > 0:
            print('增量：(%s)' % source_list_)
            # print(source_list_)
            # source_list_.drop(labels='exists',axis=1,inplace=True)
            source_list_.to_sql('pledge_stat', con=engine, index=False, index_label=['ts_code', 'end_date'], if_exists='append')
            time.sleep(2)




def insert_history_data_pledge_stat(engine, pro):
    """
    保存历史数据，通过对比添加数据
    """
    # 股票列表
    sql = 'select s.ts_code from stock s'
    stock_list = pd.read_sql(sql, engine)
    if len(stock_list) > 0:
        for i in range(0, len(stock_list)):
            ts_code = stock_list.loc[i].ts_code
            deal_append(ts_code, '', engine, pro)





def update_current_data_pledge_stat(engine, pro):
    """
    更新当前数据，后续使用定时器
    """
    day_updates = 10 #设定更新30天数据
    current_day = choose_day(0).strftime('%Y%m%d')
    that_day = choose_day(day_updates).strftime('%Y%m%d')
    sql = 'select t.cal_date from trade_cal t WHERE t.cal_date >= ' + that_day + ' and t.cal_date <=' + current_day
    df = pd.read_sql(sql, engine)
    if len(df) > 0:
        for i in range(0, len(df)):
            deal_append('', df.loc[i].cal_date, engine, pro)